package com.ht.aems.fesmodel.persist;

import com.ht.aems.dbservice.ConnectionFactory;
import com.ht.aems.dbservice.SqlExecutor;
import com.ht.aems.fesmodel.FesPersistence;
import com.ht.aems.fesmodel.MeasPersistence;
import com.ht.aems.fesmodel.data.AnalogPoint;
import com.ht.aems.fesmodel.data.DiscretePoint;
import com.ht.aems.fesmodel.data.MeasurePoint;
import com.ht.aems.fesmodel.data.SOEData;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;

/**
 * Created by IntelliJ IDEA.
 *
 * @author Dong Shufeng
 *         Date: 2008-7-22
 */
public class MeasSqlPersist implements MeasPersistence {//todo: yanxue
    private static Logger log = Logger.getLogger(MeasSqlPersist.class);
    String database;
    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");


    public MeasSqlPersist() {
        ResourceBundle rb = ResourceBundle.getBundle("config.fes");
        this.database = rb.getString("fes.fesdb.name").trim();
    }

    public MeasSqlPersist(String database) {
        this.database = database;
    }

    public boolean saveSOEs(SOEData[] yxs) {
        List<String> batch = new ArrayList<String>();
        Connection conn = database != null ? ConnectionFactory.createConnection(database) : ConnectionFactory.createConnection();
        if (conn == null) {
            log.info("Connection can not be built, save yx_soe failed ...");
            return false;
        }
        log.info("Start save yx_soe ...");
        for (SOEData yx : yxs) {
            for (int i = 0; i < yx.getData().length; i++) {
                int nid = MeasurePoint.getNID(yx.getRemoteUnitId(), yx.getYxBit()[i]);
                batch.add("INSERT INTO " + FesPersistence.TABLE_SOE + "(nId,nRtu,nPnt,nValue,soetime)values (" + nid +
                        "," + yx.getRemoteUnitId() + "," + yx.getYxBit()[i] +
                        "," + yx.getData()[i] + ",\'" + format.format(new java.util.Date(yx.getSoeTimes()[i])) + "\')");
            }
        }

        if (SqlExecutor.executeBatch(conn, batch.toArray(new String[]{}), false)) {
            SqlExecutor.close(conn);
            log.info("save yx_soe success ...");
            return true;
        } else {
            SqlExecutor.close(conn);
            log.info("save yx_soe failed ...");
            return false;
        }
    }

    public AnalogPoint[] getAnalogPoints(int rtuId) {//todo, test is needed
        List<AnalogPoint> l = new ArrayList<AnalogPoint>();
        Connection conn = database != null ? ConnectionFactory.createConnection(database) : ConnectionFactory.createConnection();
        ResultSet r = null;
        try {
            r = SqlExecutor.executeQuery(conn, "select * from " + FesPersistence.TABLE_ALG + " where nRtu=" + rtuId + " order by nId");
            while (r.next()) {
                AnalogPoint p = getAnalogPoint(r);
                l.add(p);
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.warn(e);
        } finally {
            try {
                if (r != null)
                    r.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                log.warn(e);
            }
        }
        return l.toArray(new AnalogPoint[]{});
    }

    private AnalogPoint getAnalogPoint(ResultSet r) throws SQLException {
        AnalogPoint p = new AnalogPoint();
        p.setId(r.getInt("nId"));
        p.setName(r.getString("sId") == null ? "" : r.getString("sId").trim());
        p.setRtuId(r.getInt("NRTU"));
        p.setPointNum(r.getInt("nPnt"));
        p.setBitNum(r.getInt("NBITNUM"));
        p.setScale(r.getFloat("fScal"));
        p.setOffset(r.getFloat("fOffset"));
        p.setEngValue(r.getFloat("FENG"));
        p.setDisplayValue(r.getFloat("FDISPLAY"));
        p.setManualValue(r.getFloat("FMANUAL"));

        p.setInvert(r.getInt("nInvert") == 1);
        p.setManual(r.getInt("nIsManual") == 1);
        p.setAlarmDisabled(r.getInt("nIsAlarmDisabled") == 1);
        p.setOffline(r.getInt("NISOFFLINE") == 1);
        p.setSuspect(r.getInt("NISSUSPECT") == 1);

        p.setUpperLimit1(r.getFloat("fUpperLimit1"));
        p.setUpperLimit2(r.getFloat("fUpperLimit2"));
        p.setUpperLimit3(r.getFloat("fUpperLimit3"));
        p.setLowerLimit1(r.getFloat("fLowerLimit1"));
        p.setLowerLimit2(r.getFloat("fLowerLimit2"));
        p.setLowerLimit3(r.getFloat("fLowerLimit3"));
        p.setTransmitToId(r.getString("sTransmitToId") == null ? "" : r.getString("sTransmitToId"));
        p.setTransmitFromId(r.getString("sTransmitFromId") == null ? "" : r.getString("sTransmitFromId"));
        String type = r.getString("sHisSamplePeriod");
        p.setMeasureType((type == null || type.trim().equals("")) ? MeasurePoint.MINUTE : Integer.parseInt(type.split(";")[0]));
        return p;
    }

    private DiscretePoint getDiscretePoint(ResultSet r) throws SQLException {
        DiscretePoint p = new DiscretePoint();
        p.setId(r.getInt("nId"));
        p.setName(r.getString("sId") == null ? "" : r.getString("sId").trim());
        p.setRtuId(r.getInt("NRTU"));
        p.setPointNum(r.getInt("nPnt"));
        p.setEngValue(r.getInt("NVALUE"));
        p.setManualValue(r.getInt("NMANUALVALUE"));
        p.setBitNum(r.getInt("NBITNUM"));

        p.setAlarmDisabled(r.getInt("nIsAlarmDisabled") == 1);
        p.setManual(r.getInt("NISMANUAL") == 1);
        p.setOffline(r.getInt("NISOFFLINE") == 1);
        p.setSuspect(r.getInt("NISSUSPECT") == 1);
        p.setMeasureType(r.getInt("nHisTimeInterval"));
        p.setTransmitToId(r.getString("sTransmitToId") == null ? "" : r.getString("sTransmitToId"));
        p.setTransmitFromId(r.getString("sTransmitFromId") == null ? "" : r.getString("sTransmitFromId"));
        return p;
    }

    public DiscretePoint[] getDiscretePoints(int rtuId) {
//        return new DiscretePoint[0];  //To change body of implemented methods use File | Settings | File Templates.
        List<DiscretePoint> l = new ArrayList<DiscretePoint>();
        Connection conn = database != null ? ConnectionFactory.createConnection(database) : ConnectionFactory.createConnection();
        ResultSet r = null;
        try {
            r = SqlExecutor.executeQuery(conn, "select * from " + FesPersistence.TABLE_DIS + " where nRtu=" + rtuId + " order by nId");
            while (r.next()) {
                DiscretePoint p = getDiscretePoint(r);
                l.add(p);
            }
        } catch (Exception e) {
            log.warn(e);
        } finally {
            try {
                if (r != null)
                    r.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                log.warn(e);
            }
        }
        return l.toArray(new DiscretePoint[]{});
    }

    public AnalogPoint getAnalogPoint(int rtuId, int pointNum) {
        AnalogPoint[] analogpoints = getAnalogPoints(rtuId);
        if (analogpoints != null && analogpoints.length > 0) {
            for (AnalogPoint analogPoint : analogpoints) {
                if (analogPoint.getPointNum() == pointNum)
                    return analogPoint;

            }
        }
        return null;
    }

    public DiscretePoint getDiscretePoint(int rtuId, int pointNum) {
        DiscretePoint[] discretePoints = getDiscretePoints(rtuId);
        if (discretePoints != null && discretePoints.length > 0) {
            for (DiscretePoint discretePoint : discretePoints) {
                if (discretePoint.getPointNum() == pointNum)
                    return discretePoint;

            }
        }
        return null;
    }

    public AnalogPoint getAnalogPoint(int pointId) {
        Connection conn = database != null ? ConnectionFactory.createConnection(database) : ConnectionFactory.createConnection();
        ResultSet r = null;
        try {
            r = SqlExecutor.executeQuery(conn, "select * from " + FesPersistence.TABLE_ALG + " where nid=" + pointId);
            while (r.next()) {
                AnalogPoint p = getAnalogPoint(r);
                return p;
            }
        } catch (Exception e) {
            log.warn(e);
        } finally {
            try {
                if (r != null)
                    r.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                log.warn(e);
            }

        }
        return null;
    }

    public DiscretePoint getDiscretePoint(int pointId) {
        Connection conn = database != null ? ConnectionFactory.createConnection(database) : ConnectionFactory.createConnection();
        ResultSet r = null;
        try {
            r = SqlExecutor.executeQuery(conn, "select * from " + FesPersistence.TABLE_DIS + " where nid=" + pointId);
            r.next();
            return getDiscretePoint(r);
        } catch (Exception e) {
            log.warn(e);
        } finally {
            try {
                if (r != null)
                    r.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                log.warn(e);
                e.printStackTrace();
            }
        }
        return null;
    }

    public boolean addAnalogPoint(AnalogPoint p) {
        return addAnalogPoints(new AnalogPoint[]{p});
    }

    public boolean addAnalogPoints(AnalogPoint[] points) {//todo, test is needed
        String[] batch = new String[points.length * 2];
        int count = 0;
        for (AnalogPoint p : points) {
            batch[count++] = "delete from " + FesPersistence.TABLE_ALG + " where nId=" + p.getId();
            batch[count++] = "insert into " + FesPersistence.TABLE_ALG + "(nId, sId, nRtu, nPnt, nBitnum,fScal, fOffset, " +
                    "FENG,FDISPLAY,FMANUAL," +
                    "nInvert,nIsManual, nIsAlarmDisabled,NISOFFLINE,NISSUSPECT," +
                    "fUpperLimit1, fUpperLimit2,fUpperLimit3, " +
                    //"fLowerLimit1, fLowerLimit2, fLowerLimit3, sHisSamplePeriod) VALUES(" +
                    "fLowerLimit1, fLowerLimit2, fLowerLimit3, sHisSamplePeriod, sTransmitToId, sTransmitFromId) VALUES(" +
                    p.getId() + ",'" + p.getName() + "'," + p.getRtuId() + "," + p.getPointNum() + "," + p.getBitNum() + "," + p.getScale() + "," + p.getOffset() + "," +
                    p.getEngValue() + "," + p.getDisplayValue() + "," + p.getManualValue() + "," +
                    (p.isInvert() ? "1," : "0,") + (p.isManual() ? "1," : "0,") + (p.isAlarmDisabled() ? "1," : "0,") + (p.isOffline() ? "1," : "0,") + (p.isSuspect() ? "1," : "0,") +
                    p.getUpperLimit1() + "," + p.getUpperLimit2() + "," + p.getUpperLimit3() + "," +
                    //p.getLowerLimit1() + "," + p.getLowerLimit2() + "," + p.getLowerLimit3() + ",'" + p.getHisSamplePeriod() + "')";
                    p.getLowerLimit1() + "," + p.getLowerLimit2() + "," + p.getLowerLimit3() + ",'" + p.getMeasureType() + "','" + p.getTransmitToId() + "','" + p.getTransmitFromId() + "')";
        }
        //for(String s : batch)
        //    System.out.println(s + "**************************");
        if (database != null)
            return SqlExecutor.executeBatch(database, batch);
        else
            return SqlExecutor.executeBatch(batch);
    }


    public boolean addDiscretePoint(DiscretePoint p) {
        return addDiscretePoints(new DiscretePoint[]{p});
    }

    public boolean addDiscretePoints(DiscretePoint[] points) {
        String[] batch = new String[points.length * 2];
        int count = 0;
        for (DiscretePoint p : points) {
            batch[count++] = "delete from " + FesPersistence.TABLE_DIS + " where nId=" + p.getId();
            batch[count++] = "insert into " + FesPersistence.TABLE_DIS + "(nId, sId, nRtu, nPnt,nBitnum, " +
                    "NISMANUAL, NISOFFLINE, NISSUSPECT,NISALARMDISABLED," +
                    "NVALUE,NMANUALVALUE,nHisTimeInterval, sTransmitToId,sTransmitFromId) VALUES(" +
                    p.getId() + ", '" + p.getName() + "' ," + p.getRtuId() + " ," + p.getPointNum() + " ," + p.getBitNum() + " ," +
                    (p.isManual() ? 1 : 0) + " ," + (p.isOffline() ? 1 : 0) + " ," + (p.isSuspect() ? "1" : "0") + " ," + (p.isAlarmDisabled() ? "1" : "0") + " ," +
                    p.getEngValue() + "," + p.getManualValue() + "," + p.getMeasureType() + ",'" + p.getTransmitToId() + "','" + p.getTransmitFromId() + "')";
        }
        if (database != null)
            return SqlExecutor.executeBatch(database, batch);
        else
            return SqlExecutor.executeBatch(batch);
    }

    public boolean removeAnalogPoint(int pointId) {
        if (database != null)
            return SqlExecutor.executeSql(database, "delete from " + FesPersistence.TABLE_ALG + " where nId=" + pointId);
        else
            return SqlExecutor.executeSql("delete from " + FesPersistence.TABLE_ALG + " where nId=" + pointId);
    }

    public boolean removeDiscretePoint(int pointId) {
        if (database != null)
            return SqlExecutor.executeSql(database, "delete from " + FesPersistence.TABLE_DIS + " where nId=" + pointId);
        else
            return SqlExecutor.executeSql("delete from " + FesPersistence.TABLE_DIS + " where nId=" + pointId);
    }

    public boolean removeRtu(int rtuId) {
        String[] batch = new String[3];
        int count = 0;
        batch[count++] = "delete from " + FesPersistence.TABLE_COMMINFO + " where nId=" + rtuId;
        batch[count++] = "delete from " + FesPersistence.TABLE_ALG + " where nRtu=" + rtuId;
        batch[count] = "delete from " + FesPersistence.TABLE_DIS + " where nRtu=" + rtuId;
        if (database != null)
            return SqlExecutor.executeBatch(database, batch);
        else
            return SqlExecutor.executeBatch(batch);
    }

    public AnalogPoint[] getAllAnalogPoints() {
        List<AnalogPoint> l = new ArrayList<AnalogPoint>();
        Connection conn = database != null ? ConnectionFactory.createConnection(database) : ConnectionFactory.createConnection();
        if (conn == null)
            return new AnalogPoint[0];
        ResultSet r = null;
        try {
            r = SqlExecutor.executeQuery(conn, "select * from " + FesPersistence.TABLE_ALG);
            while (r.next())
                l.add(getAnalogPoint(r));
        } catch (Exception e) {
            e.printStackTrace();
            log.warn(e);
        } finally {
            try {
                if (r != null)
                    r.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                log.warn(e);
            }
        }
        return l.toArray(new AnalogPoint[]{});
    }

    public DiscretePoint[] getAllDiscretePoints() {
        List<DiscretePoint> l = new ArrayList<DiscretePoint>();
        Connection conn = database != null ? ConnectionFactory.createConnection(database) : ConnectionFactory.createConnection();
        ResultSet r = null;
        try {
            r = SqlExecutor.executeQuery(conn, "select * from " + FesPersistence.TABLE_DIS);
            while (r.next())
                l.add(getDiscretePoint(r));
        } catch (Exception e) {
            e.printStackTrace();
            log.warn(e);
        } finally {
            try {
                if (r != null)
                    r.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                log.warn(e);
            }
        }
        return l.toArray(new DiscretePoint[]{});
    }

    public boolean updateAnalogPoint(AnalogPoint p) {
        return addAnalogPoint(p);
    }

    public boolean updateAnalogPoints(AnalogPoint[] points) {
        return addAnalogPoints(points);
    }

    public boolean updateDiscretePoint(DiscretePoint point) {
        return addDiscretePoint(point);
    }

    public boolean updateDiscretePoints(DiscretePoint[] points) {
        return addDiscretePoints(points);
    }

    public boolean updateMeasurePoint(MeasurePoint point) {
        if (point instanceof DiscretePoint) {
            DiscretePoint dpoint = (DiscretePoint) point;
            return updateDiscretePoint(dpoint);
        } else if (point instanceof AnalogPoint) {
            AnalogPoint apoint = (AnalogPoint) point;
            return updateAnalogPoint(apoint);
        }
        return false;
    }

    public boolean addMeasurePoint(MeasurePoint point) {
        if (point instanceof DiscretePoint) {
            DiscretePoint dpoint = (DiscretePoint) point;
            return addDiscretePoint(dpoint);
        } else if (point instanceof AnalogPoint) {
            AnalogPoint apoint = (AnalogPoint) point;
            return addAnalogPoint(apoint);
        }
        return false;
    }

    public boolean updateRtu(int rtuId, String name) {
        String sql = "update " + FesPersistence.TABLE_COMMINFO + " set sname='" + name + "' where nId=" + rtuId;
        if (database != null)
            return SqlExecutor.executeSql(database, sql);
        else
            return SqlExecutor.executeSql(sql);
    }

    public boolean addMeasurePoints(MeasurePoint point) {
        if (point instanceof DiscretePoint) {
            DiscretePoint dpoint = (DiscretePoint) point;
            return addDiscretePoint(dpoint);
        } else if (point instanceof AnalogPoint) {
            AnalogPoint apoint = (AnalogPoint) point;
            return addAnalogPoint(apoint);
        }
        return false;
    }

    public String getDatabase() {
        return database;
    }

    public void setDatabase(String database) {
        this.database = database;
    }

    public static void main(String[] args) {
        String sql = "INSERT INTO " + FesPersistence.TABLE_SOE + "(nId,nRtu,nPnt,nValue,soetime)values (" + 1 +
                "," + 2 + "," + 3 +
                "," + 4 + ",\'" + 6 + "\')";
        System.out.println(sql);
    }
}
